/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DAO;

import DTO.genre.Genre;
import DTO.genre.Genres;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *
 * @author tiwtiger
 */
public class GenreDAO {

    public boolean add(Genre genre){
        Connection con = null;
        PreparedStatement stm = null;

        try{
            con = DBConnection.getConnection();
            stm = con.prepareStatement("INSERT INTO [Genres](Name) VALUE(?)");
            stm.setString(1, genre.getName());

            stm.executeUpdate();
            return true;
        }catch(SQLException ex){
            System.out.println("SQL Exception Error: " + ex.getMessage());
            return false;
        }finally{
            if(con!=null){
                try{
                    con.close();
                }catch(SQLException ex){
                    System.out.println("Connection error: " + ex.getMessage());
                }
            }else if(stm!=null){
                try{
                    stm.close();
                }catch(SQLException ex){
                    System.out.println("SQL Statement error: " + ex.getMessage());
                }
            }
        }
    }

    public boolean delete(int genreID){
        Connection con = null;
        PreparedStatement stm = null;

        try{
            con = DBConnection.getConnection();
            stm = con.prepareStatement("DELETE FROM [Genres] WHERE GenreID=?");
            stm.setInt(1, genreID);
            stm.executeUpdate();
            return true;
        }catch(SQLException ex){
            System.out.println("SQL Exception error: " + ex.getMessage());
            return false;
        }finally{
            if(con!=null){
                try{
                    con.close();
                }catch(SQLException ex){
                    System.out.println("Connection error: " + ex.getMessage());
                }
            }else if(stm!=null){
                try{
                    con.close();
                }catch(SQLException ex){
                    System.out.println("SQL Statement error: " + ex.getMessage());
                }
            }
        }
    }

    public boolean update(Genre genre){
        Connection con = null;
        PreparedStatement stm = null;

        try{
            con = DBConnection.getConnection();
            stm = con.prepareStatement("UPDATE [Genres] SET Name=? WHERE GenreID=?");
            stm.setString(1, genre.getName());
            stm.setInt(2, genre.getGenreID().intValue());
            stm.executeUpdate();
            return true;
        }catch(SQLException ex){
            System.out.println("SQL Exception error: " + ex.getMessage());
            return false;
        }finally{
            if(con!=null){
                try{
                    con.close();
                }catch(SQLException ex){
                    System.out.println("Connection error: " + ex.getMessage());
                }
            }else if(stm!=null){
                try{
                    stm.close();
                }catch(SQLException ex){
                    System.out.println("SQL Statement error: " + ex.getMessage());
                }
            }
        }
    }

    public Genres selectAllGenres(){
        Connection con = null;
        PreparedStatement stm = null;
        try{
            con = DBConnection.getConnection();
            stm = con.prepareStatement("SELECT * FROM [Genres]");
            ResultSet rs = stm.executeQuery();

            Genres genres = new Genres();
            Genre genre = null;
            while(rs.next()){
                genre = new Genre();
                genre.setGenreID(rs.getBigDecimal("GenreID").toBigInteger());
                genre.setName(rs.getString("Name"));
                genres.getGenre().add(genre);
            }
            return genres;
        }catch(SQLException ex){
            System.out.println("SQL Exception ex: " + ex.getMessage());
            return null;
        }finally{
            if(con!=null){
                try{
                    con.close();
                }catch(SQLException ex){
                    System.out.println("Connection error: " + ex.getMessage());
                }
            }else if(stm!=null){
                try{
                    stm.close();
                }catch(SQLException ex){
                    System.out.println("SQL Statement error: " + ex.getMessage());
                }
            }
        }
    }
}
